SQL Encryption Key Backup

Keyfactor Command uses Microsoft SQL Server Encryption to encrypt portions of the database to protect secret data, including service account credentials. Understanding Keyfactor Command’s use of SQL Server Encryption is important to a successful disaster recovery strategy.

SQL Server Encryption uses a SQL Server instance-level service master key (SMK) and a database-level database master key (DMK) to provide the top-level encryption hierarchy used when encrypting SQL data. The DMK is protected by one or more passwords and optionally the SMK. For an application—such as Keyfactor Command—to access SQL encrypted data, the application must either provide one of the DMK passwords or ask SQL Server to access the data via the SMK. Keyfactor Command uses the SMK. For more details on the mechanics of SQL Server Encryption and related disaster recovery procedures, see the SQL Server documentation.

When the Keyfactor Command database is created, the DMK is configured to be protected by the SMK and then the DMK password is set to a random value, which is not retained. This means the only way to get to the encrypted data is by leveraging the SMK, which happens automatically without any user interaction or the need to store the DMK password in a potentially insecure location.

Different restoration scenarios may require a backup of the SMK or the DMK or neither. Some restoration possibilities include:

  • In the case where a Keyfactor Command database needs to be restored to the same SQL server where the backup was taken and the SQL Server software itself is not being restored, the correct SMK will still be present on the SQL server and restoration of the database itself is sufficient to be able to access the encrypted data.
  • In the case where a Keyfactor Command database is being restored to a SQL Server with a different SMK (either a different SQL Server or the same SQL server that has been reinstalled or had its SMK changed), the encrypted data will be inaccessible because the server level SMK is not the same as it was when the DMK was created. In this scenario, either the DMK needs to be restored from the backup taken when the Keyfactor Command database was created or a known DMK password may be used to recover encrypted data within the Keyfactor Command database. To prepare for this scenario, the configuration wizard strongly encourages making a DMK backup when the Keyfactor Command database is created.
  • In the case where a Keyfactor Command database needs to be restored to a SQL Server with a different SMK, the DMK cannot be restored and a DMK password is not known, a backup of the SMK may be used to restore the server, but this will affect any other databases on the server that make use of SQL encryption.

If no backup of the SMK or DMK exists, all DMK passwords are unknown, and the SQL server holding the SMK is lost, the encrypted data within Keyfactor Command is not recoverable (even with a database backup.)

To backup the DMK, as a user with control permission on the SQL server where the Keyfactor Command database is select your Keyfactor Command database and run the following SQL command:

BACKUP MASTER KEY TO FILE = 'path_to_file'

ENCRYPTION BY PASSWORD = 'SecurePassword#1234'

Replace "path_to_file" with a path and filename for the output file. This can be either a local path on the SQL server or a UNC path. The selected output directory must be writable by the service account under which SQL Server is running. By default, the SQL backup directory has appropriate permissions. Replace "SecurePassword#1234' with a secure password to protect the file. Store the backup file and the password in a safe, well-documented location. For more information, see:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-a-database-master-key?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-master-key-transact-sql?view=sql-server-2017

To backup the SMK, as a user with control server permission run the following SQL command on the SQL server where the Keyfactor Command database is:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'

ENCRYPTION BY PASSWORD = 'SecurePassword#1234'

Replace "path_to_file" with a path and filename for the output file. This can be either a local path on the SQL server or a UNC path. The selected output directory must be writable by the service account under which SQL Server is running. By default, the SQL backup directory has appropriate permissions. Replace "SecurePassword#1234' with a secure password to protect the file. Store the backup file and the password in a safe, well-documented location. For more information, see:

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql?view=sql-server-2017

To prepare for disaster recovery, you should have the DMK backup created during installation, an SMK backup, the passwords for these files and a recent database backup. You will likely only need either the DMK or the SMK if you need to restore to a SQL server instance other than the original SQL server instance, but it can be useful to have the flexibility to choose between the two at restoration time. If you need to restore to the original SQL server instance, you will only need a recent database backup and not either of the database keys. For information about restoring using the DMK or SMK, see Disaster Recovery.